﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.SERVICE.DAL;
using KPIS.GERP.SERVICE.Model;

namespace KPIS.GERP.SERVICE.DAL
{
    public class Employee
    {
        Common common = new Common();
        DBManager dbManager = new DBManager(DataProvider.MySQL, ConfigurationSettings.AppSettings["ConnectionString"]);

        /// <summary>
        /// 
        /// </summary>
        /// <param name="empSeq"></param>
        /// <param name="empTypeSeq"></param>
        /// <param name="classSeq"></param>
        /// <param name="posSeq"></param>
        /// <param name="workingJobSiteSeq"></param>
        /// <param name="execPosSeq"></param>
        /// <param name="empStatusSeq"></param>
        /// <param name="active">1=ปฏิบัติงานอยู่ 0=ออกจากหน่วยงานไปแล้ว</param>
        /// <param name="isGetMasNamePrefixes"></param>
        /// <param name="isGetMasGenders"></param>
        /// <param name="isGetMasReligions"></param>
        /// <param name="isGetMasNationalitiesRace"></param>
        /// <param name="isGetMasNationalities"></param>
        /// <param name="isGetHrmPsEmployeeTypes"></param>
        /// <param name="isGetHrmMpClasses"></param>
        /// <param name="isGetHrmMpClassesOld"></param>
        /// <param name="isGetHrmMpPositions"></param>
        /// <param name="isGetHrmMpJobSites"></param>
        /// <param name="isGetHrmPsEmployeeStatuses"></param>
        /// <param name="isGetMasBloodGroups"></param>
        /// <param name="isGetHrmMpExecPositions"></param>
        /// <param name="isGetMasMaritalStatuses"></param>
        /// <returns></returns>
        public List<hrm_ps_employees> GetHrmPsEmployee(string empSeq, string empTypeSeq, string classSeq
            , string posSeq, string workingJobSiteSeq, string execPosSeq, string empStatusSeq, string active
            , bool isGetMasNamePrefixes, bool isGetMasGenders, bool isGetMasReligions, bool isGetMasNationalitiesRace
            , bool isGetMasNationalities, bool isGetHrmPsEmployeeTypes, bool isGetHrmMpClasses, bool isGetHrmMpClassesOld
            , bool isGetHrmMpPositions, bool isGetHrmMpJobSites, bool isGetHrmPsEmployeeStatuses, bool isGetMasBloodGroups
            , bool isGetHrmMpExecPositions, bool isGetMasMaritalStatuses)
        {
            List<hrm_ps_employees> list;

            // Load employee hrm_ps_employees, mas_name_prefixes, hrm_ps_employee_statuses
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@emp_seq", empSeq);
                dbManager.AddParameters(2, "@emp_type_seq", empTypeSeq);
                dbManager.AddParameters(3, "@class_seq", classSeq);
                dbManager.AddParameters(4, "@pos_seq", posSeq);
                dbManager.AddParameters(5, "@working_job_site_seq", workingJobSiteSeq);
                dbManager.AddParameters(6, "@emp_status_seq", empStatusSeq);
                dbManager.AddParameters(7, "@exec_pos_seq", execPosSeq);
                dbManager.AddParameters(8, "@active", active);

                string strSQL = "select e.*, es.active"
                    + ", concat(coalesce(np.prefix_short, np.prefix_name, np.prefix_short_eng, np.prefix_name_eng, '')"
                        + ", coalesce(first_name, first_name_eng, ''), ' ', coalesce(last_name, last_name_eng, '')) as emp_name"
                    + " from hrm_ps_employees e"
                    + " left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq"
                    + " left join hrm_ps_employee_statuses es on e.emp_status_seq = es.emp_status_seq"
                    + " where e.record_status <> @delete_status"
                    + (!string.IsNullOrWhiteSpace(empSeq) ? " and e.emp_seq = @emp_seq" : "")
                    + (!string.IsNullOrWhiteSpace(empTypeSeq) ? " and e.emp_type_seq = @emp_type_seq" : "")
                    + (!string.IsNullOrWhiteSpace(classSeq) ? " and e.class_seq = @class_seq" : "")
                    + (!string.IsNullOrWhiteSpace(posSeq) ? " and e.pos_seq = @pos_seq" : "")
                    + (!string.IsNullOrWhiteSpace(workingJobSiteSeq) ? " and e.working_job_site_seq = @working_job_site_seq" : "")
                    + (!string.IsNullOrWhiteSpace(empStatusSeq) ? " and e.emp_status_seq = @emp_status_seq" : "")
                    + (!string.IsNullOrWhiteSpace(execPosSeq) ? " and e.exec_pos_seq = @exec_pos_seq" : "")
                    + (!string.IsNullOrWhiteSpace(active) ? " and es.active = @active" : "");

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                list = (List<hrm_ps_employees>)CollectionHelper.ConvertTo<hrm_ps_employees>(dtb);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            List<hrm_mp_positions> posList = GetHrmMpPositions(null
                , null, null, null, null, null, null, null, false, false, false, true, false, false);
            // Load optional
            foreach (hrm_ps_employees info in list)
            {
                if (isGetMasNamePrefixes && info.name_prefix_seq != null)
                {
                    List<mas_name_prefixes> subList = GetMasNamePrefixes(info.name_prefix_seq.ToString(), null, false);
                    info.mas_name_prefixes = subList.Count > 0 ? subList[0] : null;
                }

                if (isGetMasGenders && info.gender_seq != null)
                {
                    List<mas_genders> subList = GetMasGenders(info.gender_seq.ToString());
                    info.mas_genders = subList.Count > 0 ? subList[0] : null;
                }

                if (isGetMasReligions && info.religion_seq != null)
                {
                    //List<mas_genders> subList = GetMasGenders(info.gender_seq.ToString());
                    //info.mas_genders = subList.Count > 0 ? subList[0] : null;
                }

                if (isGetMasNationalitiesRace && info.race_nat_seq != null)
                {
                    //List<mas_genders> subList = GetMasGenders(info.gender_seq.ToString());
                    //info.mas_genders = subList.Count > 0 ? subList[0] : null;
                }

                if (isGetMasNationalities && info.nat_seq != null)
                {
                    //List<mas_genders> subList = GetMasGenders(info.gender_seq.ToString());
                    //info.mas_genders = subList.Count > 0 ? subList[0] : null;
                }

                if (isGetHrmPsEmployeeTypes && info.emp_type_seq != null)
                {
                    List<hrm_ps_employee_types> subList = GetHrmPsEmployeeTypes(info.emp_type_seq.ToString());
                    info.hrm_ps_employee_types = subList.Count > 0 ? subList[0] : null;
                }

                if (isGetHrmMpClasses && info.class_seq != null)
                {
                    List<hrm_mp_classes> subList = GetHrmMpClasses(info.class_seq.ToString()
                        , null, null, false, false);
                    info.hrm_mp_classes = subList.Count > 0 ? subList[0] : null;
                }

                if (isGetHrmMpClassesOld && info.old_class_seq != null)
                {
                    List<hrm_mp_classes> subList = GetHrmMpClasses(info.old_class_seq.ToString()
                        , null, null, false, false);
                    info.hrm_mp_classes_old = subList.Count > 0 ? subList[0] : null;
                }

                if (isGetHrmMpPositions && info.pos_seq != null)
                {
                    //List<hrm_mp_positions> subList = GetHrmMpPositions(info.pos_seq.ToString()
                    //    , null, null, null, null, null, null, null, false, false, false, true, false, false);
                    var pos1 =
                        (from pos in posList where info.pos_seq.Value == pos.pos_seq select pos).
                            FirstOrDefault();
                    info.hrm_mp_positions = pos1;
                }

                if (isGetHrmMpJobSites && info.working_job_site_seq != null)
                {
                    List<hrm_mp_job_sites> subList = GetHrmMpJobSites(info.working_job_site_seq.ToString()
                        , null, null, null, null, null, null, false, false, false, false, false, false, false, false, false, false);
                    info.hrm_mp_job_sites = subList.Count > 0 ? subList[0] : null;
                }

                if (isGetHrmPsEmployeeStatuses && info.emp_status_seq != null)
                {
                    List<hrm_ps_employee_statuses> subList = GetHrmPsEmployeeStatuses(info.emp_status_seq.ToString(), null);
                    info.hrm_ps_employee_statuses = subList.Count > 0 ? subList[0] : null;
                }

                if (isGetMasBloodGroups && info.blood_group_seq != null)
                {
                    //List<mas_genders> subList = GetMasGenders(info.gender_seq.ToString());
                    //info.mas_genders = subList.Count > 0 ? subList[0] : null;
                }

                if (isGetHrmMpExecPositions && info.exec_pos_seq != null)
                {
                    List<hrm_mp_exec_positions> subList = GetHrmMpExecPositions(info.exec_pos_seq.ToString());
                    info.hrm_mp_exec_positions = subList.Count > 0 ? subList[0] : null;
                }

                if (isGetMasMaritalStatuses && info.mar_stat_seq != null)
                {
                    //List<mas_genders> subList = GetMasGenders(info.gender_seq.ToString());
                    //info.mas_genders = subList.Count > 0 ? subList[0] : null;
                }
            }

            return list;
        }

        public List<hrm_ps_employees> GetHrmPsEmployeeLite(hrm_ps_employees filter = null, bool getInactiveEmployee = false)
        {
            List<hrm_ps_employees> list;
            if (filter == null) filter = new hrm_ps_employees();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@emp_seq", filter.emp_seq);
                dbManager.AddParameters(2, "@emp_type_seq", filter.emp_type_seq);
                dbManager.AddParameters(3, "@class_seq", filter.class_seq);
                dbManager.AddParameters(4, "@pos_seq", filter.pos_seq);
                dbManager.AddParameters(5, "@working_job_site_seq", filter.working_job_site_seq);
                dbManager.AddParameters(6, "@emp_status_seq", filter.emp_status_seq);
                dbManager.AddParameters(7, "@exec_pos_seq", filter.exec_pos_seq);
                dbManager.AddParameters(8, "@active", filter.hrm_ps_employee_statuses != null ? filter.hrm_ps_employee_statuses.active : "0");
                dbManager.AddParameters(9, "@citizen", filter.citizen);

                string strSQL = "select e.*, es.active\r\n"
                    + ", concat(coalesce(np.prefix_short, np.prefix_name, np.prefix_short_eng, np.prefix_name_eng, '')\r\n"
                        + ", coalesce(first_name, first_name_eng, ''), ' ', coalesce(last_name, last_name_eng, '')) as emp_name\r\n"
                    + " from hrm_ps_employees e\r\n"
                    + " left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq\r\n"
                    + " left join hrm_ps_employee_statuses es on e.emp_status_seq = es.emp_status_seq\r\n"
                    + " where e.record_status <> @delete_status\r\n"
                    + (filter.emp_seq != 0 ? " and e.emp_seq = @emp_seq\r\n" : "")
                    + (!common.IsIntNullOrZero(filter.emp_type_seq) ? " and e.emp_type_seq = @emp_type_seq\r\n" : "")
                    + (!common.IsIntNullOrZero(filter.class_seq) ? " and e.class_seq = @class_seq\r\n" : "")
                    + (!common.IsIntNullOrZero(filter.pos_seq) ? " and e.pos_seq = @pos_seq\r\n" : "")
                    + (!common.IsIntNullOrZero(filter.working_job_site_seq) ? " and e.working_job_site_seq = @working_job_site_seq\r\n" : "")
                    + (!common.IsIntNullOrZero(filter.emp_status_seq) ? " and e.emp_status_seq = @emp_status_seq\r\n" : "")
                    + (!common.IsIntNullOrZero(filter.exec_pos_seq) ? " and e.exec_pos_seq = @exec_pos_seq\r\n" : "")
                    + (filter.hrm_ps_employee_statuses != null && !string.IsNullOrWhiteSpace(filter.hrm_ps_employee_statuses.active) ? " and es.active = @active\r\n" : "")
                    + (!string.IsNullOrWhiteSpace(filter.citizen) ? " and e.citizen = @citizen\r\n" : "");

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                list = (List<hrm_ps_employees>)CollectionHelper.ConvertTo<hrm_ps_employees>(dtb);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) { dbManager.Connection.Close(); dbManager.Dispose(); }
            }

            List<mas_name_prefixes> namePrefixeses = GetMasNamePrefixes(null, null, false);
            List<mas_genders> genderses = GetMasGenders(null);
            List<mas_religions> religions = GetMasReligions(null);
            List<mas_nationalities> nationalities = GetMasNationalities(null);
            List<hrm_ps_employee_types> hrmPsEmployeeTypeses = GetHrmPsEmployeeTypes(null);
            List<hrm_mp_classes> hrmMpClasse = GetHrmMpClasses(null, null, null, false, false);
            List<hrm_mp_positions> positionses = GetHrmMpPositions(null, null, null, null, null, null, null, null, false, false, false,true, true, false);
            List<hrm_mp_job_sites> jobSiteses = GetHrmMpJobSites(null, null, null, null, null, null, null, false, false, false, false, false, false, false, false, false, false);
            List<hrm_ps_employee_statuses> empStatus = GetHrmPsEmployeeStatuses(null, null);
            List<hrm_mp_exec_positions> execPositions = GetHrmMpExecPositions(null); 

            foreach (hrm_ps_employees info in list)
            {  
                info.mas_name_prefixes =
                    namePrefixeses.FirstOrDefault(n => n.name_prefix_seq == info.name_prefix_seq);

                info.mas_genders = genderses.FirstOrDefault(g => g.gender_seq == info.gender_seq);

                info.mas_religions = religions.FirstOrDefault(r => r.religion_seq == info.religion_seq);

                info.mas_nationalities = nationalities.FirstOrDefault(n => n.nat_seq == info.nat_seq);

                info.hrm_ps_employee_types =
                    hrmPsEmployeeTypeses.FirstOrDefault(et => et.emp_type_seq == info.emp_type_seq);

                info.hrm_mp_classes = hrmMpClasse.FirstOrDefault(c => c.class_seq == info.class_seq);

                info.hrm_mp_classes_old = hrmMpClasse.FirstOrDefault(c => c.class_seq == info.old_class_seq);

                info.hrm_mp_classes_old = hrmMpClasse.FirstOrDefault(c => c.class_seq == info.old_class_seq);

                info.hrm_mp_positions = positionses.FirstOrDefault(p => p.pos_seq == info.pos_seq);

                if (info.hrm_mp_positions != null)
                {
                    info.hrm_mp_positions.hrm_mp_positions_working_upper =
                        positionses.FirstOrDefault(p => p.pos_seq == info.hrm_mp_positions.pos_working_upper_seq); 

                }

                info.hrm_mp_job_sites = jobSiteses.FirstOrDefault(j => j.job_site_seq == info.working_job_site_seq);

                info.hrm_ps_employee_statuses = empStatus.FirstOrDefault(s => s.emp_status_seq == info.emp_status_seq);

                info.hrm_mp_exec_positions = execPositions.FirstOrDefault(x => x.exec_pos_seq == info.exec_pos_seq);
                 
            }
            return (from employee in list
                    where (employee.hrm_ps_employee_statuses != null && employee.hrm_ps_employee_statuses.active == "1") || getInactiveEmployee
                    select employee).ToList();
        }
        
        public int GetJobSiteSeqFromEmpSeq(int empSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@emp_seq", empSeq);

                string strSQL = @"select working_job_site_seq from hrm_ps_employees where emp_seq = @emp_seq and record_status != @delete_status";
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb.Rows[0][0] == DBNull.Value ? 0 : Convert.ToInt32(dtb.Rows[0][0]);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) { dbManager.Connection.Close(); dbManager.Dispose(); }
            }
        }

        public IEnumerable<hrm_mp_job_sites> GetJobSite(int jobSiteSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@job_site_seq", jobSiteSeq);

                string strSQL = @"select * from hrm_mp_job_sites where job_site_seq = @job_site_seq and record_status != @delete_status";
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return (List<hrm_mp_job_sites>)CollectionHelper.ConvertTo<hrm_mp_job_sites>(dtb);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) { dbManager.Connection.Close(); dbManager.Dispose(); }
            }
        }

        private IEnumerable<hrm_mp_job_sites> GetJobSite()
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);

                string strSQL = @"select * from hrm_mp_job_sites where record_status != @delete_status";
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return (List<hrm_mp_job_sites>)CollectionHelper.ConvertTo<hrm_mp_job_sites>(dtb);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) { dbManager.Connection.Close(); dbManager.Dispose(); }
            }
        }

        private IEnumerable<hrm_ps_employees> GetEmployee()
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);

                string strSQL = @"select * from hrm_ps_employees where record_status != @delete_status";
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return (List<hrm_ps_employees>)CollectionHelper.ConvertTo<hrm_ps_employees>(dtb);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) { dbManager.Connection.Close(); dbManager.Dispose(); }
            }
        }

        public hrm_ps_employees LoadJobSiteHeadEmployee(int jobSiteId)
        {
            var jobSiteAll = GetJobSite();
            var employeeAll = GetEmployee();
            var jobSite = jobSiteAll.FirstOrDefault(j =>
                                                     j.job_site_seq == jobSiteId &&
                                                     j.record_status != "D");
            if (jobSite == null || (jobSite.head_pos_seq == null && string.IsNullOrWhiteSpace(jobSite.job_site_full_path)))
                return null;

            if (jobSite.head_pos_seq != null)
                return employeeAll.FirstOrDefault(o => o.pos_seq == jobSite.head_pos_seq);


            // sample of path = /2/60/71/96/
            // 1. split
            // 2. exclude empty (first,last)
            // 3. exclude last element (96)
            // 4. answer = 71 60 2
            var jobSitesInPath = (jobSite.job_site_full_path ?? string.Empty).Split('/').Where(o => !string.IsNullOrWhiteSpace(o)).Reverse().Skip(1);

            var jobSiteUppers = from j in jobSiteAll
                                where jobSitesInPath.Contains(Convert.ToString(j.job_site_seq))
                                select j;

            foreach (var jobSiteUpper in jobSiteUppers)
            {
                if (jobSiteUpper.head_pos_seq != null)
                {
                    return employeeAll.FirstOrDefault(o => o.pos_seq == jobSiteUpper.head_pos_seq);
                }
            }
            return null;
        }


        public sys_sc_users GetHrmPsUser(string empSeq)
        {
            sys_sc_users result = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@emp_seq", empSeq);

                const string strSQL = "select * from sys_sc_users where record_status <> @delete_status and emp_seq = @emp_seq ";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                DataRow dr;
                if (dtb.Rows.Count > 0 && (dr = dtb.Rows[0]) != null)
                {
                    result = new sys_sc_users
                        {
                            user_seq = (int) dr["user_seq"],
                            emp_seq = (int) dr["emp_seq"],
                            user_code = Convert.ToString(dr["user_code"]),
                            user_password = Convert.ToString(dr["user_password"]),
                            user_first_name = Convert.ToString(dr["user_first_name"]),
                            user_last_name = Convert.ToString(dr["user_last_name"]),
                            user_citizen = Convert.ToString(dr["user_citizen"]),
                            user_job_site_seq = dr["user_job_site_seq"] != DBNull.Value ? (int?) dr["user_job_site_seq"] : null,
                            user_pos_seq = dr["user_pos_seq"] != DBNull.Value ? (int?) dr["user_pos_seq"] : null
                        };
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return result;
        }

        public List<sys_sc_users> GetUsers()
        {
            List<sys_sc_users> result = new List<sys_sc_users>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus); 

                const string strSQL = "select * from sys_sc_users where record_status <> @delete_status ";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                
                foreach(DataRow dr in dtb.Rows)
                {
                    result.Add(new sys_sc_users
                        {
                            user_seq = (int) dr["user_seq"],
                            emp_seq = (int) dr["emp_seq"],
                            user_code = Convert.ToString(dr["user_code"]),
                            user_password = Convert.ToString(dr["user_password"]),
                            user_first_name = Convert.ToString(dr["user_first_name"]),
                            user_last_name = Convert.ToString(dr["user_last_name"]),
                            user_citizen = Convert.ToString(dr["user_citizen"]),
                            user_job_site_seq =
                                dr["user_job_site_seq"] != DBNull.Value ? (int?) dr["user_job_site_seq"] : null,
                            user_pos_seq = dr["user_pos_seq"] != DBNull.Value ? (int?) dr["user_pos_seq"] : null
                        });
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) { dbManager.Connection.Close(); dbManager.Dispose(); }
            }

            return result;
        }

        public List<mas_name_prefixes> GetMasNamePrefixes(string namePrefixSeq, string genderSeq, bool isGetMasGenders)
        {
            List<mas_name_prefixes> list;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@name_prefix_seq", namePrefixSeq);
                dbManager.AddParameters(2, "@gender_seq", genderSeq);

                string strSQL = "select * from mas_name_prefixes where record_status <> @delete_status"
                                +
                                (!string.IsNullOrEmpty(namePrefixSeq) ? " and name_prefix_seq = @name_prefix_seq" : "")
                                + (!string.IsNullOrEmpty(genderSeq) ? " and gender_seq = @gender_seq" : "");

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                list = new List<mas_name_prefixes>();
                foreach (DataRow dr in dtb.Rows)
                {
                    List<mas_genders> listMasGenders = null;

                    //======= CheckValid ======
                    if (isGetMasGenders)
                    {
                        listMasGenders = GetMasGenders(dr["gender_seq"].ToString());
                        isGetMasGenders = listMasGenders.Count > 0;
                    }
                    //=========================

                    list.Add(new mas_name_prefixes
                        {
                            name_prefix_seq = Convert.ToInt32(dr["name_prefix_seq"].ToString()),
                            prefix_code = dr["prefix_code"].ToString(),
                            prefix_name = dr["prefix_name"].ToString(),
                            prefix_name_eng = dr["prefix_name_eng"].ToString(),
                            prefix_short = dr["prefix_short"].ToString(),
                            prefix_short_eng = dr["prefix_short_eng"].ToString(),
                            gender_seq = common.ConvertToNullableInt(dr["gender_seq"]),
                            record_status = dr["record_status"].ToString(),
                            created_by = common.ConvertToNullableInt(dr["created_by"].ToString()),
                            created_when = common.ConvertToNullableDateTime(dr["created_when"].ToString()),
                            updated_by = common.ConvertToNullableInt(dr["updated_by"].ToString()),
                            updated_when = common.ConvertToNullableDateTime(dr["updated_when"].ToString()),
                            mas_genders = isGetMasGenders ? listMasGenders[0] : null
                        });
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return list;
        }

        public List<mas_genders> GetMasGenders(string genderSeq)
        {
            List<mas_genders> list;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@gender_seq", genderSeq);

                string strSQL = "select * from mas_genders where record_status <> @delete_status"
                    + (!string.IsNullOrEmpty(genderSeq) ? " and gender_seq = @gender_seq" : "");

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                list = (from DataRow dr in dtb.Rows
                        select new mas_genders()
                            {
                                gender_seq = Convert.ToInt32(dr["gender_seq"].ToString()), 
                                gender_name = dr["gender_name"].ToString(), 
                                gender_name_eng = dr["gender_name_eng"].ToString(), 
                                gender_thai = dr["gender_thai"].ToString(), 
                                record_status = dr["record_status"].ToString(), 
                                created_by = common.ConvertToNullableInt(dr["created_by"].ToString()), 
                                created_when = common.ConvertToNullableDateTime(dr["created_when"].ToString()), 
                                updated_by = common.ConvertToNullableInt(dr["updated_by"].ToString()), 
                                updated_when = common.ConvertToNullableDateTime(dr["updated_when"].ToString())
                            }).ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return list;
        }

        public List<mas_religions> GetMasReligions(string religionSeq)
        {
            List<mas_religions> list;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@religion_seq", religionSeq);

                string strSQL = "select * from mas_religions where record_status <> @delete_status"
                                + (!string.IsNullOrEmpty(religionSeq) ? " and religion_seq = @religion_seq" : "");

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                list = (from DataRow dr in dtb.Rows
                        select new mas_religions
                            {
                                religion_seq = Convert.ToInt32(dr["religion_seq"].ToString()),
                                religion_code = dr["religion_code"].ToString(),
                                religion_name = dr["religion_name"].ToString(),
                                religion_name_eng = dr["religion_name_eng"].ToString(),
                                record_status = dr["record_status"].ToString(),
                                created_by = common.ConvertToNullableInt(dr["created_by"].ToString()),
                                created_when = common.ConvertToNullableDateTime(dr["created_when"].ToString()),
                                updated_by = common.ConvertToNullableInt(dr["updated_by"].ToString()),
                                updated_when = common.ConvertToNullableDateTime(dr["updated_when"].ToString())
                            }).ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return list;
        }

        public List<mas_nationalities> GetMasNationalities(string natSeq)
        {
            var list = new List<mas_nationalities>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@nat_seq", natSeq);

                string strSQL = "select * from mas_nationalities where record_status <> @delete_status"
                    + (!string.IsNullOrEmpty(natSeq) ? " and nat_seq = @nat_seq" : "");
                
                list.AddRange(from DataRow dr in dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows
                              select new mas_nationalities
                                  {
                                      nat_seq = Convert.ToInt32(dr["nat_seq"].ToString()), 
                                      nat_name = dr["nat_name"].ToString(), 
                                      nat_name_eng = dr["nat_name_eng"].ToString(), 
                                      record_status = dr["record_status"].ToString(), 
                                      created_by = common.ConvertToNullableInt(dr["created_by"].ToString()), 
                                      created_when = common.ConvertToNullableDateTime(dr["created_when"].ToString()),
                                      updated_by = common.ConvertToNullableInt(dr["updated_by"].ToString()), 
                                      updated_when = common.ConvertToNullableDateTime(dr["updated_when"].ToString())
                                  });
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }
            return list;
        }

        public List<hrm_ps_employee_types> GetHrmPsEmployeeTypes(string empTypeSeq)
        {
            var list = new List<hrm_ps_employee_types>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@emp_type_seq", empTypeSeq);

                string strSQL = "select * from hrm_ps_employee_types where record_status <> @delete_status"
                    + (!string.IsNullOrEmpty(empTypeSeq) ? " and emp_type_seq = @emp_type_seq" : "");
                
                list.AddRange(from DataRow dr in dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows
                              select new hrm_ps_employee_types
                                  {
                                      emp_type_seq = Convert.ToInt32(dr["emp_type_seq"].ToString()), 
                                      emp_type_name = dr["emp_type_name"].ToString(), 
                                      salary_up_time = common.ConvertToNullableInt(dr["salary_up_time"]), 
                                      retirements = common.ConvertToNullableInt(dr["retirements"]), 
                                      royalitems = common.ConvertToNullableInt(dr["royalitems"]), 
                                      year_type_seq = common.ConvertToNullableInt(dr["year_type_seq"]),
                                      remarks = dr["remarks"].ToString(), 
                                      record_status = dr["record_status"].ToString(), 
                                      created_by = common.ConvertToNullableInt(dr["created_by"].ToString()),
                                      created_when = common.ConvertToNullableDateTime(dr["created_when"].ToString()), 
                                      updated_by = common.ConvertToNullableInt(dr["updated_by"].ToString()),
                                      updated_when = common.ConvertToNullableDateTime(dr["updated_when"].ToString())
                                  });

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return list;
        }

        public List<hrm_mp_position_types> GetHrmMpPositionTypes(string posTypeSeq, string empTypeSeq
            , bool isGetHrmPsEmployeeTypes)
        {
            var list = new List<hrm_mp_position_types>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@pos_type_seq", posTypeSeq);
                dbManager.AddParameters(1, "@emp_type_seq", empTypeSeq);

                string strSQL = "select * from hrm_mp_position_types where record_status <> @delete_status"
                                + (!string.IsNullOrEmpty(posTypeSeq) ? " and pos_type_seq = @pos_type_seq" : "")
                                + (!string.IsNullOrEmpty(empTypeSeq) ? " and emp_type_seq = @emp_type_seq" : "");

                foreach (DataRow dr in dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows)
                {
                    List<hrm_ps_employee_types> listHrmPsEmployeeTypes = null;

                    //======= CheckValid ======
                    if (isGetHrmPsEmployeeTypes)
                    {
                        listHrmPsEmployeeTypes = GetHrmPsEmployeeTypes(dr["emp_type_seq"].ToString());
                        isGetHrmPsEmployeeTypes = listHrmPsEmployeeTypes.Count > 0;
                    }

                    list.Add(new hrm_mp_position_types
                        {
                            pos_type_seq = Convert.ToInt32(dr["pos_type_seq"].ToString()),
                            pos_type_name = dr["pos_type_name"].ToString(),
                            pos_type_name_short = dr["pos_type_name_short"].ToString(),
                            pos_type_eng = dr["pos_type_eng"].ToString(),
                            pos_type_eng_short = dr["pos_type_eng_short"].ToString(),
                            emp_type_seq = common.ConvertToNullableInt(dr["emp_type_seq"]),
                            remarks = dr["remarks"].ToString(),
                            record_status = dr["record_status"].ToString(),
                            created_by = common.ConvertToNullableInt(dr["created_by"].ToString()),
                            created_when = common.ConvertToNullableDateTime(dr["created_when"].ToString()),
                            updated_by = common.ConvertToNullableInt(dr["updated_by"].ToString()),
                            updated_when = common.ConvertToNullableDateTime(dr["updated_when"].ToString()),
                            hrm_ps_employee_types = isGetHrmPsEmployeeTypes ? listHrmPsEmployeeTypes[0] : null
                        });
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return list;
        }

        public List<hrm_mp_classes> GetHrmMpClasses(string classSeq, string posTypeSeq, string empTypeSeq
            , bool isGetHrmMpPositionTypes, bool isGetHrmPsEmployeeTypes)
        {
            var list = new List<hrm_mp_classes>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@class_seq", classSeq);
                dbManager.AddParameters(2, "@pos_type_seq", posTypeSeq);
                dbManager.AddParameters(3, "@emp_type_seq", empTypeSeq);

                string strSQL = "select * from hrm_mp_classes where record_status <> @delete_status"
                    + (!string.IsNullOrEmpty(classSeq) ? " and class_seq = @class_seq" : "")
                    + (!string.IsNullOrEmpty(posTypeSeq) ? " and pos_type_seq = @pos_type_seq" : "")
                    + (!string.IsNullOrEmpty(empTypeSeq) ? " and emp_type_seq = @emp_type_seq" : "");

                
                foreach (DataRow dr in dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows)
                {
                    List<hrm_ps_employee_types> listHrmPsEmployeeTypes = null;
                    List<hrm_mp_position_types> listHrmMpPositionTypes = null;

                    //======= CheckValid ======
                    if (isGetHrmMpPositionTypes)
                    {
                        listHrmMpPositionTypes = GetHrmMpPositionTypes(dr["pos_type_seq"].ToString(), null, false);
                        isGetHrmMpPositionTypes = listHrmMpPositionTypes.Count > 0;
                    }

                    if (isGetHrmPsEmployeeTypes)
                    {
                        listHrmPsEmployeeTypes = GetHrmPsEmployeeTypes(dr["emp_type_seq"].ToString());
                        isGetHrmPsEmployeeTypes = listHrmPsEmployeeTypes.Count > 0;
                    }
                    //=========================

                    list.Add(new hrm_mp_classes
                    {
                        class_seq = Convert.ToInt32(dr["class_seq"].ToString()),
                        class_no = dr["class_no"].ToString(),
                        class_name = dr["class_name"].ToString(),
                        class_name_short = dr["class_name_short"].ToString(),
                        class_eng = dr["class_eng"].ToString(),
                        class_eng_short = dr["class_eng_short"].ToString(),
                        pos_type_seq = common.ConvertToNullableInt(dr["pos_type_seq"]),
                        emp_type_seq = common.ConvertToNullableInt(dr["emp_type_seq"]),
                        remarks = dr["remarks"].ToString(),
                        record_status = dr["record_status"].ToString(),
                        created_by = common.ConvertToNullableInt(dr["created_by"].ToString()),
                        created_when = common.ConvertToNullableDateTime(dr["created_when"].ToString()),
                        updated_by = common.ConvertToNullableInt(dr["updated_by"].ToString()),
                        updated_when = common.ConvertToNullableDateTime(dr["updated_when"].ToString()),
                        hrm_mp_position_types = (isGetHrmMpPositionTypes ? listHrmMpPositionTypes[0] : null),
                        hrm_ps_employee_types = (isGetHrmPsEmployeeTypes ? listHrmPsEmployeeTypes[0] : null)
                    });
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }
            return list;
        }

        public List<FieldPosition> GetHrmMpFieldPositions(string fieldPosSeq, string pos_type_seq
            , string emp_type_seq, string field_group_seq
            , bool isGetHrmPsEmployeeTypes, bool isGetHrmMpPositionTypes, bool isGetHrmMpFieldGroups)
        {
            DataTable dtb;
            List<FieldPosition> list = new List<FieldPosition>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@field_pos_seq", fieldPosSeq);
                dbManager.AddParameters(2, "@pos_type_seq", pos_type_seq);
                dbManager.AddParameters(3, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(4, "@field_group_seq", field_group_seq);

                string strSQL = "select * from hrm_mp_field_positions where record_status <> @delete_status"
                    + (!string.IsNullOrEmpty(fieldPosSeq) ? " and field_pos_seq = @field_pos_seq" : "")
                    + (!string.IsNullOrEmpty(pos_type_seq) ? " and pos_type_seq = @pos_type_seq" : "")
                    + (!string.IsNullOrEmpty(emp_type_seq) ? " and emp_type_seq = @emp_type_seq" : "")
                    + (!string.IsNullOrEmpty(field_group_seq) ? " and field_group_seq = @field_group_seq" : "")
                    ;

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                foreach (DataRow dr in dtb.Rows)
                {
                    List<hrm_ps_employee_types> list_hrm_ps_employee_types = null;
                    List<hrm_mp_position_types> list_hrm_mp_position_types = null;
                    List<hrm_mp_field_groups> list_hrm_mp_field_groups = null;

                    //======= CheckValid ======
                    if (isGetHrmMpPositionTypes)
                    {
                        list_hrm_mp_position_types = GetHrmMpPositionTypes(dr["pos_type_seq"].ToString(), null, false);
                        isGetHrmMpPositionTypes = list_hrm_mp_position_types.Count > 0;
                    }

                    if (isGetHrmPsEmployeeTypes)
                    {
                        list_hrm_ps_employee_types = GetHrmPsEmployeeTypes(dr["emp_type_seq"].ToString());
                        isGetHrmPsEmployeeTypes = list_hrm_ps_employee_types.Count > 0;
                    }

                    if (isGetHrmMpFieldGroups)
                    {
                        //list_hrm_mp_field_groups = GetHrmPsEmployeeTypes(dr["emp_type_seq"].ToString());
                        //isGetHrmMpFieldGroups = list_hrm_mp_field_groups.Count > 0;
                    }
                    //=========================

                    list.Add(new FieldPosition()
                    {
                        field_pos_seq = Convert.ToInt32(dr["field_pos_seq"]),
                        field_pos_code = dr["field_pos_code"].ToString(),
                        field_pos_name = dr["field_pos_name"].ToString(),
                        field_pos_name_eng = dr["field_pos_name_eng"].ToString(),
                        field_pos_line = dr["field_pos_line"].ToString(),
                        field_pos_line_short = dr["field_pos_line_short"].ToString(),
                        field_pos_line_eng = dr["field_pos_line_eng"].ToString(),
                        emp_type_seq = common.ConvertToNullableInt(dr["emp_type_seq"]),
                        pos_type_seq = common.ConvertToNullableInt(dr["pos_type_seq"]),
                        field_group_seq = common.ConvertToNullableInt(dr["field_group_seq"]),
                        class_spec_seq = common.ConvertToNullableInt(dr["class_spec_seq"]),
                        field_pos_seq_new = common.ConvertToNullableInt(dr["field_pos_seq_new"]),
                        remarks = dr["remarks"].ToString(),

                        record_status = dr["record_status"].ToString(),
                        created_by = common.ConvertToNullableInt(dr["created_by"].ToString()),
                        created_when = common.ConvertToNullableDateTime(dr["created_when"].ToString()),
                        updated_by = common.ConvertToNullableInt(dr["updated_by"].ToString()),
                        updated_when = common.ConvertToNullableDateTime(dr["updated_when"].ToString()),

                        hrm_ps_employee_types = isGetHrmPsEmployeeTypes ? list_hrm_ps_employee_types[0] : null,
                        hrm_mp_position_types = isGetHrmMpPositionTypes ? list_hrm_mp_position_types[0] : null,
                        hrm_mp_field_groups = isGetHrmMpFieldGroups ? list_hrm_mp_field_groups[0] : null,
                        ClassSpecification = null
                    });
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return list;
        }
        
        //*
        public List<hrm_mp_positions> GetHrmMpPositions(string pos_seq, string field_pos_seq, string exec_pos_seq
            , string emp_type_seq, string job_site_seq, string pos_status, string pos_upper_seq, string pos_working_upper_seq
            , bool isGetHrmMpFieldPositions, bool isGetHrmMpExecPositions, bool isGetHrmPsEmployeeTypes
            , bool isGetHrmMpJobSites, bool isGetHrmMpPositionsUpper, bool isGetHrmMpPositionsWorkingUpper)
        {
            var list = new List<hrm_mp_positions>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@pos_seq", pos_seq);
                dbManager.AddParameters(2, "@field_pos_seq", field_pos_seq);
                dbManager.AddParameters(3, "@exec_pos_seq", exec_pos_seq);
                dbManager.AddParameters(4, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(5, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(6, "@pos_status", pos_status);
                dbManager.AddParameters(7, "@pos_upper_seq", pos_upper_seq);
                dbManager.AddParameters(8, "@pos_working_upper_seq", pos_working_upper_seq);

                string strSql = "select * from hrm_mp_positions where record_status <> @delete_status"
                    + (!string.IsNullOrEmpty(pos_seq) ? " and pos_seq = @pos_seq" : "")
                    + (!string.IsNullOrEmpty(field_pos_seq) ? " and field_pos_seq = @field_pos_seq" : "")
                    + (!string.IsNullOrEmpty(exec_pos_seq) ? " and exec_pos_seq = @exec_pos_seq" : "")
                    + (!string.IsNullOrEmpty(emp_type_seq) ? " and emp_type_seq = @emp_type_seq" : "")
                    + (!string.IsNullOrEmpty(job_site_seq) ? " and job_site_seq = @job_site_seq" : "")
                    + (!string.IsNullOrEmpty(pos_status) ? " and pos_status = @pos_status" : "")
                    + (!string.IsNullOrEmpty(pos_upper_seq) ? " and pos_upper_seq = @pos_upper_seq" : "")
                    + (!string.IsNullOrEmpty(pos_working_upper_seq) ? " and pos_working_upper_seq = @pos_working_upper_seq" : "")
                    ;

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSql).Tables[0];

                foreach (DataRow dr in dtb.Rows)
                {
                    List<FieldPosition> list_hrm_mp_field_positions = null;
                    List<hrm_mp_exec_positions> list_hrm_mp_exec_positions = null;
                    List<hrm_ps_employee_types> list_hrm_ps_employee_types = null;
                    List<hrm_mp_job_sites> list_hrm_mp_job_sites = null;
                    List<hrm_mp_positions> list_hrm_mp_positions_upper = null;
                    List<hrm_mp_positions> list_hrm_mp_positions_working_upper = null;

                    //======= CheckValid ======
                    if (isGetHrmMpFieldPositions)
                    {
                        list_hrm_mp_field_positions = GetHrmMpFieldPositions(dr["field_pos_seq"].ToString()
                            , null, null, null, false, false, false);
                        isGetHrmMpFieldPositions = list_hrm_mp_field_positions.Count > 0;
                    }

                    if (isGetHrmMpExecPositions)
                    {
                        //list_hrm_mp_exec_positions = GetHrmPsEmployeeTypes(dr["exec_pos_seq"].ToString());
                        //isGetHrmMpExecPositions = list_hrm_mp_exec_positions.Count > 0;
                    }

                    if (isGetHrmPsEmployeeTypes)
                    {
                        list_hrm_ps_employee_types = GetHrmPsEmployeeTypes(dr["emp_type_seq"].ToString());
                        isGetHrmPsEmployeeTypes = list_hrm_ps_employee_types.Count > 0;
                    }

                    if (isGetHrmMpJobSites)
                    {
                        list_hrm_mp_job_sites = GetHrmMpJobSites(dr["job_site_seq"].ToString(),
                            "", "", "", "","","",false,false,false,false,false,false,false,false,false,false);
                        isGetHrmMpJobSites = list_hrm_mp_job_sites.Count > 0;
                    }

                    if (isGetHrmMpPositionsUpper)
                    {
                        //list_hrm_mp_positions_upper = GetHrmPsEmployeeTypes(dr["pos_upper_seq"].ToString());
                        //isGetHrmMpPositionsUpper = list_hrm_mp_positions_upper.Count > 0;
                    }

                    if (isGetHrmMpPositionsWorkingUpper)
                    {
                        //list_hrm_mp_positions_working_upper = GetHrmPsEmployeeTypes(dr["pos_working_upper_seq"].ToString());
                        //isGetHrmMpPositionsWorkingUpper = list_hrm_mp_positions_working_upper.Count > 0;
                    }
                    //=========================

                    list.Add(new hrm_mp_positions()
                    {
                        pos_seq = Convert.ToInt32(dr["pos_seq"]),
                        pos_ref = dr["pos_ref"].ToString(),
                        pos_name = dr["pos_name"].ToString(),
                        field_pos_seq = common.ConvertToNullableInt(dr["field_pos_seq"]),
                        exec_pos_seq = common.ConvertToNullableInt(dr["exec_pos_seq"]),
                        emp_type_seq = common.ConvertToNullableInt(dr["emp_type_seq"]),
                        date_defined = common.ConvertToNullableDateTime(dr["date_defined"]),
                        date_money_support = common.ConvertToNullableDateTime(dr["date_money_support"]),
                        date_changed = common.ConvertToNullableDateTime(dr["date_changed"]),
                        change_ref_no = dr["change_ref_no"].ToString(),
                        salary_quota = common.ConvertToNullableDouble(dr["salary_quota"]),
                        last_salary_quota = common.ConvertToNullableDouble(dr["last_salary_quota"]),
                        job_site_seq = common.ConvertToNullableInt(dr["job_site_seq"]),
                        pos_status = dr["pos_status"].ToString(),
                        salary_min = common.ConvertToNullableDouble(dr["salary_min"]),
                        salary_max = common.ConvertToNullableDouble(dr["salary_max"]),
                        pos_exec_salary = common.ConvertToNullableDouble(dr["pos_exec_salary"]),
                        class_spec_seq = common.ConvertToNullableInt(dr["class_spec_seq"]),
                        pos_upper_seq = common.ConvertToNullableInt(dr["pos_upper_seq"]),
                        pos_working_upper_seq = common.ConvertToNullableInt(dr["pos_working_upper_seq"]),
                        job_description = dr["job_description"].ToString(),
                        skill_seq = common.ConvertToNullableInt(dr["skill_seq"]),
                        salary_glow = common.ConvertToNullableDouble(dr["remarks"]),
                        currency_seq = common.ConvertToNullableInt(dr["currency_seq"]),
                        remarks = dr["remarks"].ToString(),

                        record_status = dr["record_status"].ToString(),
                        created_by = common.ConvertToNullableInt(dr["created_by"].ToString()),
                        created_when = common.ConvertToNullableDateTime(dr["created_when"].ToString()),
                        updated_by = common.ConvertToNullableInt(dr["updated_by"].ToString()),
                        updated_when = common.ConvertToNullableDateTime(dr["updated_when"].ToString()),

                        FieldPosition = isGetHrmMpFieldPositions &&
                            list_hrm_mp_field_positions != null &&
                            list_hrm_mp_field_positions.Count > 0 
                            ? list_hrm_mp_field_positions[0] : null,

                        hrm_mp_exec_positions = isGetHrmMpExecPositions &&
                            list_hrm_mp_exec_positions != null &&
                            list_hrm_mp_exec_positions.Count > 0
                            ? list_hrm_mp_exec_positions[0] : null,

                        hrm_ps_employee_types = isGetHrmPsEmployeeTypes &&
                            list_hrm_ps_employee_types != null &&
                            list_hrm_ps_employee_types.Count > 0
                            ? list_hrm_ps_employee_types[0] : null,

                        hrm_mp_job_sites = isGetHrmMpJobSites &&
                            list_hrm_mp_job_sites != null &&
                            list_hrm_mp_job_sites.Count > 0
                            ? list_hrm_mp_job_sites[0] : null,

                        hrm_mp_positions_upper = isGetHrmMpPositionsUpper &&
                            list_hrm_mp_positions_upper != null &&
                            list_hrm_mp_positions_upper.Count > 0
                            ? list_hrm_mp_positions_upper[0] : null,

                        hrm_mp_positions_working_upper = isGetHrmMpPositionsWorkingUpper &&
                            list_hrm_mp_positions_working_upper != null &&
                            list_hrm_mp_positions_working_upper.Count > 0
                            ? list_hrm_mp_positions_working_upper[0] : null,

                        ClassSpecification = null,
                        hrm_mp_position_skill= null
                    });
                }

            }
            catch (Exception ex)
            {
                /*here*/
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return list;
        }

        public List<hrm_mp_job_sites> GetHrmMpJobSites(string job_site_seq, string job_site_upper_seq, string job_site_type_seq
            , string work_area_seq, string job_site_level_seq, string job_site_ref_seq, string head_pos_seq
            , bool isGetHrmMpJobSitesUpper, bool isGetHrmMpJobSiteTypes, bool isGetHrmMpWorkAreas
            , bool isGetHrmMpJobSiteLevels, bool isGetHrmMpJobSitesRef, bool isGetHrmMpPositionsHeader
            , bool isGetMasDistricts, bool isGetMasProvinces, bool isGetMasCities, bool isGetMasCountries)
        {
            DataTable dtb;
            List<hrm_mp_job_sites> list = new List<hrm_mp_job_sites>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(2, "@job_site_upper_seq", job_site_upper_seq);
                dbManager.AddParameters(3, "@job_site_type_seq", job_site_type_seq);
                dbManager.AddParameters(4, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(5, "@job_site_level_seq", job_site_level_seq);
                dbManager.AddParameters(6, "@job_site_ref_seq", job_site_ref_seq);
                dbManager.AddParameters(7, "@head_pos_seq", head_pos_seq);

                string strSQL = "select * from hrm_mp_job_sites where record_status <> @delete_status"
                    + (!string.IsNullOrEmpty(job_site_seq) ? " and job_site_seq = @job_site_seq" : "")
                    + (!string.IsNullOrEmpty(job_site_upper_seq) ? " and job_site_upper_seq = @job_site_upper_seq" : "")
                    + (!string.IsNullOrEmpty(job_site_type_seq) ? " and job_site_type_seq = @job_site_type_seq" : "")
                    + (!string.IsNullOrEmpty(work_area_seq) ? " and work_area_seq = @work_area_seq" : "")
                    + (!string.IsNullOrEmpty(job_site_level_seq) ? " and job_site_level_seq = @job_site_level_seq" : "")
                    + (!string.IsNullOrEmpty(job_site_ref_seq) ? " and job_site_ref_seq = @job_site_ref_seq" : "")
                    + (!string.IsNullOrEmpty(head_pos_seq) ? " and head_pos_seq = @head_pos_seq" : "")
                    ;

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                list = (List<hrm_mp_job_sites>)CollectionHelper.ConvertTo<hrm_mp_job_sites>(dtb);

                foreach (hrm_mp_job_sites info in list)
                {
                    if (isGetHrmMpJobSitesUpper && info.job_site_upper_seq != null)
                    {
                        List<hrm_mp_job_sites> subList = GetHrmMpJobSites(info.job_site_upper_seq.ToString()
                            , null, null, null, null, null, null, false, false, false, false, false, false, false, false, false, false);

                        info.hrm_mp_job_sites_upper = subList.Count > 0 ? subList[0] : null;
                    }

                    if (isGetHrmMpJobSiteTypes && info.job_site_type_seq != null)
                    {
                        List<hrm_mp_job_site_types> subList = GetHrmMpJobSiteTypes(info.job_site_type_seq.ToString(), false);

                        info.hrm_mp_job_site_types = subList.Count > 0 ? subList[0] : null;
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return list;
        }

        public List<hrm_mp_job_site_types> GetHrmMpJobSiteTypes(string job_site_type_seq, bool isGetCurrentHrmMpJobSites)
        {
            DataTable dtb;
            List<hrm_mp_job_site_types> list = new List<hrm_mp_job_site_types>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@job_site_type_seq", job_site_type_seq);

                string strSQL = "select * from hrm_mp_job_sites where record_status <> @delete_status"
                    + (!string.IsNullOrEmpty(job_site_type_seq) ? " and job_site_type_seq = @job_site_type_seq" : "")
                    ;

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                list = (List<hrm_mp_job_site_types>)CollectionHelper.ConvertTo<hrm_mp_job_site_types>(dtb);

                foreach (hrm_mp_job_site_types info in list)
                {
                    if (isGetCurrentHrmMpJobSites && info.curr_job_site_seq != null)
                    {
                        List<hrm_mp_job_sites> subList = GetHrmMpJobSites(info.curr_job_site_seq.ToString()
                            , null, null, null, null, null, null, false, false, false, false, false, false, false, false, false, false);

                        info.current_hrm_mp_job_sites = subList.Count > 0 ? subList[0] : null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return list;
        }

        public List<hrm_ps_employee_statuses> GetHrmPsEmployeeStatuses(string emp_status_seq, string active)
        {
            DataTable dtb;
            List<hrm_ps_employee_statuses> list = new List<hrm_ps_employee_statuses>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@emp_status_seq", emp_status_seq);
                dbManager.AddParameters(2, "@active", active);

                string strSQL = "select * from hrm_ps_employee_statuses where record_status <> @delete_status"
                    + (!string.IsNullOrEmpty(emp_status_seq) ? " and emp_status_seq = @emp_status_seq" : "")
                    + (!string.IsNullOrEmpty(active) ? " and active = @active" : "")
                    ;

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                list = (List<hrm_ps_employee_statuses>)CollectionHelper.ConvertTo<hrm_ps_employee_statuses>(dtb);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return list;
        }

        public List<hrm_mp_exec_positions> GetHrmMpExecPositions(string exec_pos_seq)
        {
            DataTable dtb;
            List<hrm_mp_exec_positions> list = new List<hrm_mp_exec_positions>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@exec_pos_seq", exec_pos_seq);

                string strSQL = "select * from hrm_mp_exec_positions where record_status <> @delete_status"
                    + (!string.IsNullOrEmpty(exec_pos_seq) ? " and exec_pos_seq = @exec_pos_seq" : "")
                    ;

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                list = (List<hrm_mp_exec_positions>)CollectionHelper.ConvertTo<hrm_mp_exec_positions>(dtb);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return list;
        }

        public List<hrm_ps_employees> GetEmployee(string emp_seq, string emp_type_seq, string class_seq
            , string pos_seq, string working_job_site_seq, string exec_pos_seq, string emp_status_seq
            , bool isGetMasNamePrefixes, bool isGetMasGenders, bool isGetPosition = false
            , bool isGetUser = false)
        {
            DataTable dtb;
            List<hrm_ps_employees> list = new List<hrm_ps_employees>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(3, "@class_seq", class_seq);
                dbManager.AddParameters(4, "@pos_seq", pos_seq);
                dbManager.AddParameters(5, "@working_job_site_seq", working_job_site_seq);
                dbManager.AddParameters(6, "@emp_status_seq", emp_status_seq);
                dbManager.AddParameters(7, "@exec_pos_seq", exec_pos_seq);

                string strSQL = "select * from hrm_ps_employees where record_status <> @delete_status"
                    + (!string.IsNullOrWhiteSpace(emp_seq) ? " and emp_seq = @emp_seq" : "")
                    + (!string.IsNullOrWhiteSpace(emp_type_seq) ? " and emp_type_seq = @emp_type_seq" : "")
                    + (!string.IsNullOrWhiteSpace(class_seq) ? " and class_seq = @class_seq" : "")
                    + (!string.IsNullOrWhiteSpace(pos_seq) ? " and pos_seq = @pos_seq" : "")
                    + (!string.IsNullOrWhiteSpace(working_job_site_seq) ? " and working_job_site_seq = @working_job_site_seq" : "")
                    + (!string.IsNullOrWhiteSpace(emp_status_seq) ? " and emp_status_seq = @emp_status_seq" : "")
                    + (!string.IsNullOrWhiteSpace(exec_pos_seq) ? " and exec_pos_seq = @exec_pos_seq" : "");

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0]; 
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            var namePrefixesList = GetMasNamePrefixes(null, null, false);
            var gendersList = GetMasGenders(null);
            var positionsList = GetHrmMpPositions(null, "", "", "", "", "",
                        "", "", false, false, false, true, true, true);
            foreach (DataRow dr in dtb.Rows)
            {
                List<mas_name_prefixes> list_mas_name_prefixes = null;
                List<mas_genders> list_mas_genders = null;
                List<hrm_mp_positions> list_position = null;

                //======= CheckValid ======
                if (isGetMasNamePrefixes)
                {
                    list_mas_name_prefixes = GetMasNamePrefixes(dr["name_prefix_seq"].ToString(), null, false);
                    isGetMasNamePrefixes = list_mas_name_prefixes.Count > 0;
                }

                if (isGetMasGenders)
                {
                    list_mas_genders = GetMasGenders(dr["gender_seq"].ToString());
                    isGetMasGenders = list_mas_genders.Count > 0;
                }
                if (isGetPosition)
                {
                    /*hear*/
                    list_position = GetHrmMpPositions(dr["pos_seq"].ToString(), "", "", "", "", "",
                        "", "", false, false, false, true, true, true);
                    isGetPosition = list_position.Count > 0;
                }
                //=========================

                list.Add(new hrm_ps_employees
                {
                    emp_seq = Convert.ToInt32(dr["emp_seq"]),
                    name_prefix_seq = common.ConvertToNullableInt(dr["name_prefix_seq"]),
                    first_name = dr["first_name"].ToString(),
                    last_name = dr["last_name"].ToString(),
                    first_name_eng = dr["first_name_eng"].ToString(),
                    last_name_eng = dr["last_name_eng"].ToString(),
                    nick_name = dr["nick_name"].ToString(),
                    gender_seq = common.ConvertToNullableInt(dr["gender_seq"]),
                    religion_seq = common.ConvertToNullableInt(dr["religion_seq"]),
                    race_nat_seq = common.ConvertToNullableInt(dr["race_nat_seq"]),
                    nat_seq = common.ConvertToNullableInt(dr["nat_seq"]),
                    birth_date = common.ConvertToNullableDateTime(dr["birth_date"]),
                    emp_type_seq = common.ConvertToNullableInt(dr["emp_type_seq"]),
                    class_seq = common.ConvertToNullableInt(dr["class_seq"]),
                    salary_level_seq = common.ConvertToNullableInt(dr["salary_level_seq"]),
                    salary = common.ConvertToNullableDouble(dr["salary"]),
                    pos_seq = common.ConvertToNullableInt(dr["pos_seq"]),
                    working_job_site_seq = common.ConvertToNullableInt(dr["working_job_site_seq"]),
                    working_job_site_text = dr["working_job_site_text"].ToString(),
                    date_admitted = common.ConvertToNullableDateTime(dr["date_admitted"]),
                    date_work_start = common.ConvertToNullableDateTime(dr["date_work_start"]),
                    date_quit = common.ConvertToNullableDateTime(dr["date_quit"]),
                    emp_status_seq = common.ConvertToNullableInt(dr["emp_status_seq"]),
                    emp_sec_seq = common.ConvertToNullableInt(dr["emp_sec_seq"]),
                    citizen = dr["citizen"].ToString(),
                    tax_payer = dr["tax_payer"].ToString(),
                    social = dr["social"].ToString(),
                    official = dr["official"].ToString(),
                    blood_group_seq = common.ConvertToNullableInt(dr["blood_group_seq"]),
                    probation_date_end = common.ConvertToNullableDateTime(dr["probation_date_end"]),
                    death_date = common.ConvertToNullableDateTime(dr["death_date"]),
                    date_latest_pos = common.ConvertToNullableDateTime(dr["date_latest_pos"]),
                    date_latest_class = common.ConvertToNullableDateTime(dr["date_latest_class"]),
                    date_latest_working_job_site = common.ConvertToNullableDateTime(dr["date_latest_working_job_site"]),
                    exec_pos_seq = common.ConvertToNullableInt(dr["exec_pos_seq"]),
                    fund_member = common.ConvertToNullableInt(dr["fund_member"]),
                    mar_stat_seq = common.ConvertToNullableInt(dr["mar_stat_seq"]),
                    move_seq = common.ConvertToNullableInt(dr["move_seq"]),
                    old_class_seq = common.ConvertToNullableInt(dr["old_class_seq"]),
                    code_scan = dr["code_scan"].ToString(),
                    cmd_ref_no = dr["cmd_ref_no"].ToString(),
                    remarks = dr["remarks"].ToString(),
                    record_status = dr["record_status"].ToString(),
                    created_by = common.ConvertToNullableInt(dr["created_by"]),
                    created_when = common.ConvertToNullableDateTime(dr["created_when"]),
                    updated_by = common.ConvertToNullableInt(dr["updated_by"]),
                    updated_when = common.ConvertToNullableDateTime(dr["updated_when"]),
                    image_unique = dr["image_unique"].ToString(),
                    mas_name_prefixes = (isGetMasNamePrefixes ? list_mas_name_prefixes[0] : null),
                    mas_genders = (isGetMasGenders ? list_mas_genders[0] : null),
                    hrm_mp_positions = (isGetPosition ? list_position[0] : null)
                });
            }
            return list;
        }

        public List<hrm_mp_positions> GetApprovalTeam(string approvalTeamSeq)
        {
            var results = new List<hrm_mp_positions>();
            var employeeDb = new Employee();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@approval_team_seq", approvalTeamSeq);

                var strSQL = new StringBuilder();

                strSQL.Append(@"SELECT 
  t.`approval_team_seq`,
  t.`name`,
  t.`remarks`,
  tm.`approver_type`,
  tm.`approver_ref`");
                strSQL.Append(" from hrm_mp_positions t");
                strSQL.Append(" inner join approval_team_mapping tm on t.approval_team_seq = tm.approval_team_seq");
                strSQL.Append(" where t.record_status <> @delete_status ");
                strSQL.Append((!string.IsNullOrEmpty(approvalTeamSeq)
                                   ? " and approval_team_seq = @approval_team_seq"
                                   : string.Empty));
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL.ToString()).Tables[0];

                foreach (DataRow dr in dtb.Rows)
                {
                    var posSeq = (int) dr["approver_ref"];

                    var position = employeeDb.GetHrmMpPositions(posSeq.ToString(), string.Empty, string.Empty,
                                                                string.Empty, string.Empty, string.Empty,
                                                                string.Empty, string.Empty, false, false,
                                                                false, false, false, false)[0];
                    results.Add(position);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return results;
        }
         

        public List<hrm_ps_employees> GetEmployeeInJobSites(string jobSiteSeq)
        {
            var results = new List<hrm_ps_employees>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@deleteStatus", Common.DeletedStatus);
                dbManager.AddParameters(1, "@jobSiteSeq", jobSiteSeq);

                var strSQL = new StringBuilder();

                strSQL.Append(@"/* /* Get Employee of jobsite*/
 
SELECT
  em.emp_seq AS em_emp_seq,
  em.name_prefix_seq AS em_name_prefix_seq,
  em.first_name AS em_first_name,
  em.last_name AS em_last_name,
  em.first_name_eng AS em_first_name_eng,
  em.last_name_eng AS em_last_name_eng,
  em.nick_name AS em_nick_name,
  em.gender_seq AS em_gender_seq,
  em.religion_seq AS em_religion_seq,
  em.race_nat_seq AS em_race_nat_seq ,
  em.nat_seq AS em_nat_seq ,
  em.birth_date AS em_birth_date,
  em.emp_type_seq AS em_emp_type_seq,
  em.class_seq AS em_class_seq,
  em.salary_level_seq AS em_salary_level_seq,
  em.salary AS em_salary,
  em.pos_seq AS em_pos_seq,
  em.working_job_site_seq AS em_working_job_site_seq,
  em.working_job_site_text AS em_working_job_site_text,
  em.date_admitted AS em_date_admitted,
  em.date_work_start AS em_date_work_start,
  em.date_quit AS em_date_quit,
  em.emp_status_seq AS em_emp_status_seq,
  em.emp_sec_seq AS em_emp_sec_seq,
  em.citizen AS em_citizen,
  em.tax_payer AS em_tax_payer,
  em.social AS em_social ,
  em.official AS em_official,
  em.blood_group_seq AS em_blood_group_seq,
  em.probation_date_end AS em_probation_date_end,
  em.death_date AS em_death_date,
  em.date_latest_pos AS em_date_latest_pos,
  em.date_latest_class AS em_date_latest_class,
  em.date_latest_working_job_site AS em_date_latest_working_job_site,
  em.exec_pos_seq AS em_exec_pos_seq,
  em.fund_member AS em_fund_member,
  em.mar_stat_seq AS em_mar_stat_seq,
  em.move_seq AS em_move_seq,
  em.old_class_seq AS em_old_class_seq,
  em.code_scan AS em_code_scan,
  em.cmd_ref_no AS em_cmd_ref_no,
  em.record_status AS em_record_status,
  em.remarks AS em_remarks,
  em.created_by AS em_created_by,
  em.created_when AS em_created_when,
  em.updated_by AS em_updated_by,
  em.updated_when AS em_updated_when,
  em.image_unique AS em_image_unique, 
  
  js.job_site_seq AS js_job_site_seq,
  js.job_site_ref AS js_job_site_ref,
  js.job_site_name AS js_job_site_name,
  js.job_site_name_short AS js_job_site_name_short,
  js.job_site_name_eng AS js_job_site_name_eng,
  js.job_site_name_eng_short AS js_job_site_name_eng_short,
  js.job_site_upper_seq AS js_job_site_upper_seq,
  js.job_site_full_path AS js_job_site_full_path,
  js.job_site_type_seq AS js_job_site_type_seq,
  js.work_area_seq AS js_work_area_seq,
  js.job_site_gfmis AS js_job_site_gfmis,
  js.job_site_level_seq AS js_job_site_level_seq,
  js.job_description AS js_job_description,
  js.address AS js_address,
  js.district_seq AS js_district_seq,
  js.prov_seq AS js_prov_seq,
  js.city_seq AS js_city_seq,
  js.country_seq AS js_country_seq ,
  js.postal_code AS js_postal_code,
  js.tel AS js_tel,
  js.fax AS js_fax ,
  js.job_site_ref_seq AS js_job_site_ref_seq,
  js.date_start AS js_date_start,
  js.date_end AS js_date_end,
  js.job_site_stat_seq AS js_job_site_stat_seq,
  js.currency_seq AS js_currency_seq,
  js.remarks AS js_remarks,
  js.record_status AS js_record_status,
  js.created_by AS js_created_by,
  js.created_when AS js_created_when,
  js.updated_by AS js_updated_by,
  js.updated_when AS js_updated_when,
  js.address2 AS js_address2,
  js.webaddress AS js_webaddress,
  js.head_pos_seq AS js_head_pos_seq,


  ps.pos_seq AS ps_pos_seq,
  ps.pos_ref AS ps_pos_ref ,
  ps.pos_name AS ps_pos_name,
  ps.field_pos_seq AS ps_field_pos_seq,
  ps.exec_pos_seq AS ps_exec_pos_seq,
  ps.emp_type_seq AS ps_emp_type_seq,
  ps.date_defined AS ps_date_defined,
  ps.date_money_support AS ps_date_money_support,
  ps.date_changed AS ps_date_changed,
  ps.change_ref_no AS ps_change_ref_no,
  ps.salary_quota AS ps_salary_quota,
  ps.last_salary_quota AS ps_last_salary_quota,
  ps.job_site_seq AS ps_job_site_seq,
  ps.pos_status AS ps_pos_status,
  ps.salary_min AS ps_salary_min,
  ps.salary_max AS ps_salary_max,
  ps.pos_exec_salary AS ps_pos_exec_salary,
  ps.class_spec_seq AS ps_class_spec_seq,
  ps.pos_upper_seq AS ps_pos_upper_seq,
  ps.pos_working_upper_seq AS ps_pos_working_upper_seq,
  ps.job_description AS ps_job_description,
  ps.skill_seq AS ps_skill_seq ,
  ps.remarks AS ps_remarks,
  ps.record_status AS ps_record_status,
  ps.created_by AS ps_created_by,
  ps.created_when AS ps_created_when,
  ps.updated_by AS ps_updated_by,
  ps.updated_when AS ps_updated_when,
  ps.salary_glow AS ps_salary_glow,
  ps.currency_seq AS ps_currency_seq
  
FROM hrm_mp_job_sites js  
  INNER JOIN hrm_ps_employees em
    ON js.job_site_seq =em.working_job_site_seq  
  LEFT JOIN hrm_mp_positions ps
    ON ps.pos_seq = em.pos_seq
WHERE js.job_site_type_seq = 2 
AND js.job_site_full_path LIKE CONCAT('%/',@jobSiteSeq,'/%')

AND js.record_status <> @deleteStatus
AND em.record_status <> @deleteStatus
AND ps.record_status <> @deleteStatus
 "); 
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL.ToString()).Tables[0];

                foreach (DataRow dr in dtb.Rows)
                {
                    var employeeLoaded = new hrm_ps_employees
                        {
                            emp_seq = Convert.ToInt32(dr["em_emp_seq"]),
                            name_prefix_seq = common.ConvertToNullableInt(dr["em_name_prefix_seq"]),
                            first_name = Convert.ToString(dr["em_first_name"]),
                            last_name = Convert.ToString(dr["em_last_name"]),
                            first_name_eng = Convert.ToString(dr["em_first_name_eng"]),
                            last_name_eng = Convert.ToString(dr["em_last_name_eng"]),
                            nick_name = Convert.ToString(dr["em_nick_name"]),
                            gender_seq = common.ConvertToNullableInt(dr["em_gender_seq"]),
                            religion_seq = common.ConvertToNullableInt(dr["em_religion_seq"]),
                            race_nat_seq = common.ConvertToNullableInt(dr["em_race_nat_seq "]),
                            nat_seq = common.ConvertToNullableInt(dr["em_nat_seq "]),
                            birth_date = common.ConvertToNullableDateTime(dr["em_birth_date"]),
                            emp_type_seq = common.ConvertToNullableInt(dr["em_emp_type_seq"]),
                            class_seq = common.ConvertToNullableInt(dr["em_class_seq"]),
                            salary_level_seq = common.ConvertToNullableInt(dr["em_salary_level_seq"]),
                            salary = common.ConvertToNullableDouble(dr["em_salary"]),
                            pos_seq = common.ConvertToNullableInt(dr["em_pos_seq"]),
                            working_job_site_seq = common.ConvertToNullableInt(dr["em_working_job_site_seq"]),
                            working_job_site_text = Convert.ToString(dr["em_working_job_site_text"]),
                            date_admitted = common.ConvertToNullableDateTime(dr["em_date_admitted"]),
                            date_work_start = common.ConvertToNullableDateTime(dr["em_date_work_start"]),
                            date_quit = common.ConvertToNullableDateTime(dr["em_date_quit"]),
                            emp_status_seq = common.ConvertToNullableInt(dr["em_emp_status_seq"]),
                            emp_sec_seq = common.ConvertToNullableInt(dr["em_emp_sec_seq"]),
                            citizen = Convert.ToString(dr["em_citizen"]),
                            tax_payer = Convert.ToString(dr["em_tax_payer"]),
                            social = Convert.ToString(dr["em_social "]),
                            official = Convert.ToString(dr["em_official"]),
                            blood_group_seq = common.ConvertToNullableInt(dr["em_blood_group_seq"]),
                            probation_date_end = common.ConvertToNullableDateTime(dr["em_probation_date_end"]),
                            death_date = common.ConvertToNullableDateTime(dr["em_death_date"]),
                            date_latest_pos = common.ConvertToNullableDateTime(dr["em_date_latest_pos"]),
                            date_latest_class = common.ConvertToNullableDateTime(dr["em_date_latest_class"]),
                            date_latest_working_job_site = common.ConvertToNullableDateTime(dr["em_date_latest_working_job_site"]),
                            exec_pos_seq = common.ConvertToNullableInt(dr["em_exec_pos_seq"]),
                            fund_member = common.ConvertToNullableInt(dr["em_fund_member"]),
                            mar_stat_seq = common.ConvertToNullableInt(dr["em_mar_stat_seq"]),
                            move_seq = common.ConvertToNullableInt(dr["em_move_seq"]),
                            old_class_seq = common.ConvertToNullableInt(dr["em_old_class_seq"]),
                            code_scan = Convert.ToString(dr["em_code_scan"]),
                            cmd_ref_no = Convert.ToString(dr["em_cmd_ref_no"]),
                            record_status = Convert.ToString(dr["em_record_status"]),
                            remarks = Convert.ToString(dr["em_remarks"]),
                            created_by = common.ConvertToNullableInt(dr["em_created_by"]),
                            created_when = common.ConvertToNullableDateTime(dr["em_created_when"]),
                            updated_by = common.ConvertToNullableInt(dr["em_updated_by"]),
                            updated_when = common.ConvertToNullableDateTime(dr["em_updated_when"]),
                            image_unique = Convert.ToString(dr["em_image_unique"])
                        };

                    results.Add(employeeLoaded);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return results;
        }

        public hrm_ps_employees GetUpperEmployeeAndPositionOfPosSeq(string posSeq)
        {
            hrm_ps_employees result = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@deleteStatus", Common.DeletedStatus);
                dbManager.AddParameters(1, "@posSeq", posSeq);

                var strSQL = new StringBuilder();

                strSQL.Append(@"/* Get employee & position & upper employee, upper position of pos_seq */

SELECT
  em.emp_seq AS em_emp_seq,
  em.name_prefix_seq AS em_name_prefix_seq,
  em.first_name AS em_first_name,
  em.last_name AS em_last_name,
  em.first_name_eng AS em_first_name_eng,
  em.last_name_eng AS em_last_name_eng,
  em.nick_name AS em_nick_name,
  em.gender_seq AS em_gender_seq,
  em.religion_seq AS em_religion_seq,
  em.race_nat_seq AS em_race_nat_seq ,
  em.nat_seq AS em_nat_seq ,
  em.birth_date AS em_birth_date,
  em.emp_type_seq AS em_emp_type_seq,
  em.class_seq AS em_class_seq,
  em.salary_level_seq AS em_salary_level_seq,
  em.salary AS em_salary,
  em.pos_seq AS em_pos_seq,
  em.working_job_site_seq AS em_working_job_site_seq,
  em.working_job_site_text AS em_working_job_site_text,
  em.date_admitted AS em_date_admitted,
  em.date_work_start AS em_date_work_start,
  em.date_quit AS em_date_quit,
  em.emp_status_seq AS em_emp_status_seq,
  em.emp_sec_seq AS em_emp_sec_seq,
  em.citizen AS em_citizen,
  em.tax_payer AS em_tax_payer,
  em.social AS em_social ,
  em.official AS em_official,
  em.blood_group_seq AS em_blood_group_seq,
  em.probation_date_end AS em_probation_date_end,
  em.death_date AS em_death_date,
  em.date_latest_pos AS em_date_latest_pos,
  em.date_latest_class AS em_date_latest_class,
  em.date_latest_working_job_site AS em_date_latest_working_job_site,
  em.exec_pos_seq AS em_exec_pos_seq,
  em.fund_member AS em_fund_member,
  em.mar_stat_seq AS em_mar_stat_seq,
  em.move_seq AS em_move_seq,
  em.old_class_seq AS em_old_class_seq,
  em.code_scan AS em_code_scan,
  em.cmd_ref_no AS em_cmd_ref_no,
  em.record_status AS em_record_status,
  em.remarks AS em_remarks,
  em.created_by AS em_created_by,
  em.created_when AS em_created_when,
  em.updated_by AS em_updated_by,
  em.updated_when AS em_updated_when,
  em.image_unique AS em_image_unique,
  
  ps.pos_seq AS ps_pos_seq,
  ps.pos_ref AS ps_pos_ref ,
  ps.pos_name AS ps_pos_name,
  ps.field_pos_seq AS ps_field_pos_seq,
  ps.exec_pos_seq AS ps_exec_pos_seq,
  ps.emp_type_seq AS ps_emp_type_seq,
  ps.date_defined AS ps_date_defined,
  ps.date_money_support AS ps_date_money_support,
  ps.date_changed AS ps_date_changed,
  ps.change_ref_no AS ps_change_ref_no,
  ps.salary_quota AS ps_salary_quota,
  ps.last_salary_quota AS ps_last_salary_quota,
  ps.job_site_seq AS ps_job_site_seq,
  ps.pos_status AS ps_pos_status,
  ps.salary_min AS ps_salary_min,
  ps.salary_max AS ps_salary_max,
  ps.pos_exec_salary AS ps_pos_exec_salary,
  ps.class_spec_seq AS ps_class_spec_seq,
  ps.pos_upper_seq AS ps_pos_upper_seq,
  ps.pos_working_upper_seq AS ps_pos_working_upper_seq,
  ps.job_description AS ps_job_description,
  ps.skill_seq AS ps_skill_seq ,
  ps.remarks AS ps_remarks,
  ps.record_status AS ps_record_status,
  ps.created_by AS ps_created_by,
  ps.created_when AS ps_created_when,
  ps.updated_by AS ps_updated_by,
  ps.updated_when AS ps_updated_when,
  ps.salary_glow AS ps_salary_glow,
  ps.currency_seq AS ps_currency_seq,
  
  pu.pos_seq AS pu_pos_seq,
  pu.pos_ref AS pu_pos_ref ,
  pu.pos_name AS pu_pos_name,
  pu.field_pos_seq AS pu_field_pos_seq,
  pu.exec_pos_seq AS pu_exec_pos_seq,
  pu.emp_type_seq AS pu_emp_type_seq,
  pu.date_defined AS pu_date_defined,
  pu.date_money_support AS pu_date_money_support,
  pu.date_changed AS pu_date_changed,
  pu.change_ref_no AS pu_change_ref_no,
  pu.salary_quota AS pu_salary_quota,
  pu.last_salary_quota AS pu_last_salary_quota,
  pu.job_site_seq AS pu_job_site_seq,
  pu.pos_status AS pu_pos_status,
  pu.salary_min AS pu_salary_min,
  pu.salary_max AS pu_salary_max,
  pu.pos_exec_salary AS pu_pos_exec_salary,
  pu.class_spec_seq AS pu_class_spec_seq,
  pu.pos_upper_seq AS pu_pos_upper_seq,
  pu.pos_working_upper_seq AS pu_pos_working_upper_seq,
  pu.job_description AS pu_job_description,
  pu.skill_seq AS pu_skill_seq ,
  pu.remarks AS pu_remarks,
  pu.record_status AS pu_record_status,
  pu.created_by AS pu_created_by,
  pu.created_when AS pu_created_when,
  pu.updated_by AS pu_updated_by,
  pu.updated_when AS pu_updated_when,
  pu.salary_glow AS pu_salary_glow,
  pu.currency_seq AS pu_currency_seq,  

  eu.emp_seq AS eu_emp_seq,
  eu.name_prefix_seq AS eu_name_prefix_seq,
  eu.first_name AS eu_first_name,
  eu.last_name AS eu_last_name,
  eu.first_name_eng AS eu_first_name_eng,
  eu.last_name_eng AS eu_last_name_eng,
  eu.nick_name AS eu_nick_name,
  eu.gender_seq AS eu_gender_seq,
  eu.religion_seq AS eu_religion_seq,
  eu.race_nat_seq AS eu_race_nat_seq ,
  eu.nat_seq AS eu_nat_seq ,
  eu.birth_date AS eu_birth_date,
  eu.emp_type_seq AS eu_emp_type_seq,
  eu.class_seq AS eu_class_seq,
  eu.salary_level_seq AS eu_salary_level_seq,
  eu.salary AS eu_salary,
  eu.pos_seq AS eu_pos_seq,
  eu.working_job_site_seq AS eu_working_job_site_seq,
  eu.working_job_site_text AS eu_working_job_site_text,
  eu.date_admitted AS eu_date_admitted,
  eu.date_work_start AS eu_date_work_start,
  eu.date_quit AS eu_date_quit,
  eu.emp_status_seq AS eu_emp_status_seq,
  eu.emp_sec_seq AS eu_emp_sec_seq,
  eu.citizen AS eu_citizen,
  eu.tax_payer AS eu_tax_payer,
  eu.social AS eu_social ,
  eu.official AS eu_official,
  eu.blood_group_seq AS eu_blood_group_seq,
  eu.probation_date_end AS eu_probation_date_end,
  eu.death_date AS eu_death_date,
  eu.date_latest_pos AS eu_date_latest_pos,
  eu.date_latest_class AS eu_date_latest_class,
  eu.date_latest_working_job_site AS eu_date_latest_working_job_site,
  eu.exec_pos_seq AS eu_exec_pos_seq,
  eu.fund_member AS eu_fund_member,
  eu.mar_stat_seq AS eu_mar_stat_seq,
  eu.move_seq AS eu_move_seq,
  eu.old_class_seq AS eu_old_class_seq,
  eu.code_scan AS eu_code_scan,
  eu.cmd_ref_no AS eu_cmd_ref_no,
  eu.record_status AS eu_record_status,
  eu.remarks AS eu_remarks,
  eu.created_by AS eu_created_by,
  eu.created_when AS eu_created_when,
  eu.updated_by AS eu_updated_by,
  eu.updated_when AS eu_updated_when,
  eu.image_unique AS eu_image_unique  
  
FROM hrm_ps_employees em
  LEFT JOIN hrm_mp_positions ps
    ON ps.pos_seq = em.pos_seq
  LEFT JOIN hrm_mp_positions pu
    ON pu.pos_seq = ps.pos_working_upper_seq
  LEFT JOIN hrm_ps_employees eu
    ON eu.pos_seq = pu.pos_seq

WHERE ps.pos_seq = @posSeq
 ");
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL.ToString()).Tables[0];

                if (dtb.Rows.Count >= 0 && dtb.Rows[0]["eu_emp_seq"] != null)
                {
                    DataRow dr = dtb.Rows[0];
                    result = new hrm_ps_employees
                        {
                            emp_seq = Convert.ToInt32(dr["eu_emp_seq"]),
                            name_prefix_seq = common.ConvertToNullableInt(dr["eu_name_prefix_seq"]),
                            first_name = Convert.ToString(dr["eu_first_name"]),
                            last_name = Convert.ToString(dr["eu_last_name"]),
                            first_name_eng = Convert.ToString(dr["eu_first_name_eng"]),
                            last_name_eng = Convert.ToString(dr["eu_last_name_eng"]),
                            nick_name = Convert.ToString(dr["eu_nick_name"]),
                            gender_seq = common.ConvertToNullableInt(dr["eu_gender_seq"]),
                            religion_seq = common.ConvertToNullableInt(dr["eu_religion_seq"]),
                            race_nat_seq = common.ConvertToNullableInt(dr["eu_race_nat_seq "]),
                            nat_seq = common.ConvertToNullableInt(dr["eu_nat_seq "]),
                            birth_date = common.ConvertToNullableDateTime(dr["eu_birth_date"]),
                            emp_type_seq = common.ConvertToNullableInt(dr["eu_emp_type_seq"]),
                            class_seq = common.ConvertToNullableInt(dr["eu_class_seq"]),
                            salary_level_seq = common.ConvertToNullableInt(dr["eu_salary_level_seq"]),
                            salary = common.ConvertToNullableDouble(dr["eu_salary"]),
                            pos_seq = common.ConvertToNullableInt(dr["eu_pos_seq"]),
                            working_job_site_seq = common.ConvertToNullableInt(dr["eu_working_job_site_seq"]),
                            working_job_site_text = Convert.ToString(dr["eu_working_job_site_text"]),
                            date_admitted = common.ConvertToNullableDateTime(dr["eu_date_admitted"]),
                            date_work_start = common.ConvertToNullableDateTime(dr["eu_date_work_start"]),
                            date_quit = common.ConvertToNullableDateTime(dr["eu_date_quit"]),
                            emp_status_seq = common.ConvertToNullableInt(dr["eu_emp_status_seq"]),
                            emp_sec_seq = common.ConvertToNullableInt(dr["eu_emp_sec_seq"]),
                            citizen = Convert.ToString(dr["eu_citizen"]),
                            tax_payer = Convert.ToString(dr["eu_tax_payer"]),
                            social = Convert.ToString(dr["eu_social "]),
                            official = Convert.ToString(dr["eu_official"]),
                            blood_group_seq = common.ConvertToNullableInt(dr["eu_blood_group_seq"]),
                            probation_date_end = common.ConvertToNullableDateTime(dr["eu_probation_date_end"]),
                            death_date = common.ConvertToNullableDateTime(dr["eu_death_date"]),
                            date_latest_pos = common.ConvertToNullableDateTime(dr["eu_date_latest_pos"]),
                            date_latest_class = common.ConvertToNullableDateTime(dr["eu_date_latest_class"]),
                            date_latest_working_job_site =
                                common.ConvertToNullableDateTime(dr["eu_date_latest_working_job_site"]),
                            exec_pos_seq = common.ConvertToNullableInt(dr["eu_exec_pos_seq"]),
                            fund_member = common.ConvertToNullableInt(dr["eu_fund_member"]),
                            mar_stat_seq = common.ConvertToNullableInt(dr["eu_mar_stat_seq"]),
                            move_seq = common.ConvertToNullableInt(dr["eu_move_seq"]),
                            old_class_seq = common.ConvertToNullableInt(dr["eu_old_class_seq"]),
                            code_scan = Convert.ToString(dr["eu_code_scan"]),
                            cmd_ref_no = Convert.ToString(dr["eu_cmd_ref_no"]),
                            record_status = Convert.ToString(dr["eu_record_status"]),
                            remarks = Convert.ToString(dr["eu_remarks"]),
                            created_by = common.ConvertToNullableInt(dr["eu_created_by"]),
                            created_when = common.ConvertToNullableDateTime(dr["eu_created_when"]),
                            updated_by = common.ConvertToNullableInt(dr["eu_updated_by"]),
                            updated_when = common.ConvertToNullableDateTime(dr["eu_updated_when"]),
                            image_unique = Convert.ToString(dr["eu_image_unique"]),

                            hrm_mp_positions =  dtb.Rows[0]["pu_pos_seq"] != null ? new hrm_mp_positions
                                {
                                    pos_seq = Convert.ToInt32(dr["pu_pos_seq"]),
                                    pos_ref = Convert.ToString(dr["pu_pos_ref "]),
                                    pos_name = Convert.ToString(dr["pu_pos_name"]),
                                    field_pos_seq = common.ConvertToNullableInt(dr["pu_field_pos_seq"]),
                                    exec_pos_seq = common.ConvertToNullableInt(dr["pu_exec_pos_seq"]),
                                    emp_type_seq = common.ConvertToNullableInt(dr["pu_emp_type_seq"]),
                                    date_defined = common.ConvertToNullableDateTime(dr["pu_date_defined"]),
                                    date_money_support = common.ConvertToNullableDateTime(dr["pu_date_money_support"]),
                                    date_changed = common.ConvertToNullableDateTime(dr["pu_date_changed"]),
                                    change_ref_no = Convert.ToString(dr["pu_change_ref_no"]),
                                    salary_quota = common.ConvertToNullableInt(dr["pu_salary_quota"]),
                                    last_salary_quota = common.ConvertToNullableInt(dr["pu_last_salary_quota"]),
                                    job_site_seq = common.ConvertToNullableInt(dr["pu_job_site_seq"]),
                                    pos_status = Convert.ToString(dr["pu_pos_status"]),
                                    salary_min = common.ConvertToNullableInt(dr["pu_salary_min"]),
                                    salary_max = common.ConvertToNullableInt(dr["pu_salary_max"]),
                                    pos_exec_salary = common.ConvertToNullableInt(dr["pu_pos_exec_salary"]),
                                    class_spec_seq = common.ConvertToNullableInt(dr["pu_class_spec_seq"]),
                                    pos_upper_seq = common.ConvertToNullableInt(dr["pu_pos_upper_seq"]),
                                    pos_working_upper_seq = common.ConvertToNullableInt(dr["pu_pos_working_upper_seq"]),
                                    job_description = Convert.ToString(dr["pu_job_description"]),
                                    skill_seq = common.ConvertToNullableInt(dr["pu_skill_seq "]),
                                    remarks = Convert.ToString(dr["pu_remarks"]),
                                    record_status = Convert.ToString(dr["pu_record_status"]),
                                    created_by = common.ConvertToNullableInt(dr["pu_created_by"]),
                                    created_when = common.ConvertToNullableDateTime(dr["pu_created_when"]),
                                    updated_by = common.ConvertToNullableInt(dr["pu_updated_by"]),
                                    updated_when = common.ConvertToNullableDateTime(dr["pu_updated_when"]),
                                    salary_glow = common.ConvertToNullableInt(dr["pu_salary_glow"]),
                                    currency_seq = common.ConvertToNullableInt(dr["pu_currency_seq"]),
                                } : null
                        };
                     
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return result;
        }

        public List<hrm_ps_employees> GetEmployeesWithUpper(int? empSeq = null)
        {
            var employees = GetEmployee(null, null, null, null, null, null, null, true, true, true, false);
            var positionWithUppers = GetHrmMpPositions(null, null, null, null, null, null, null, null, false, false, false, false,
                                              false, true);

            var positionsListWithEmployeeWithUpperPositionAndEmployee = new List<hrm_ps_employees>();
            foreach(var position in positionWithUppers)
            {
                var upperPosition = position.hrm_mp_positions_working_upper;

                hrm_ps_employees employeeOfPosition =
                    (from employee in employees where employee.pos_seq == position.pos_seq select employee).
                        FirstOrDefault();

                hrm_ps_employees employeeOfUpperPosition;

                if(upperPosition != null)
                {
                    employeeOfUpperPosition =
                        (from employee in employees where employee.pos_seq == upperPosition.pos_seq select employee).
                            FirstOrDefault();
                }
            }

            return employees;
        }
    }
}
